<?php
/**
 * Class file for class SwiftTable
 *
 * @package    SwiftORM
 * @author     Stephen Gray
 * @copyright  Stephen Gray
 * @license    http://www.gnu.org/licenses/gpl.txt
 * @link       http://code.google.com/p/swiftorm
 */

/**
 * SwiftTable
 *
 * Basic table class, acts as;
 *
 *      Peer model class
 *      Singular model class
 *      Base peer/model class for custom model classes
 *
 * @package    SwiftORM
 * @author     Stephen Gray
 * @copyright  Stephen Gray
 * @license    http://www.gnu.org/licenses/gpl.txt
 * @link       http://code.google.com/p/swiftorm
 */
class SwiftTable extends SwiftBase
{
    /**
     * $_tableName
     *
     * table name of current model
     *
     * @var    string
     * @access public
     */
    public $_tableName;

    /**
     * $_modelName
     *
     * name of model class
     *
     * @var    string
     * @access public
     */
    public $_modelName;

    /**
     * $_fieldList
     *
     * array of field data for
     * current table
     *
     * @var    array
     * @access protected
     */
    protected $_fieldList = array();

    /**
     * $_valueList
     *
     * array of values for this table's
     * fields
     *
     * @var    array
     * @access protected
     */
    protected $_valueList = array();

    /**
     * $_instanceType
     *
     * peer or singular
     *
     * @var    string
     * @access protected
     */
    protected $_instanceType;

    /**
     * $_new
     *
     * whether this singular is a new instance
     *
     * @var    bool
     * @access protected
     */
    protected $_new;
   
    /**
     * void __construct ( string $modelName , resource $conn , string $instanceType = 'singular' , bool $newObject = false )
     *
     * saves the database connection, model/table
     * names and grabs a list of fields
     *
     * @param  string   $modelName
     * @param  resource $conn
     * @param  string   $instanceType
     * @param  bool     $newObject
     * @return void
     * @access public
     */
    public function __construct($modelName, $conn, $instanceType = 'singular', $newObject = false)
    {
        $this->_modelName = $modelName;
        $this->_tableName = strtolower(preg_replace(array('/[^A-Z^a-z^0-9^\/]+/','/([a-z\d])([A-Z])/','/([A-Z]+)([A-Z][a-z])/'), array('_','\1_\2','\1_\2'), $modelName));
        $this->_conn = $conn;
        $this->_instanceType = $instanceType;
        $this->_new = $newObject;

        /**
         * get list of field and their data
         */
        $fieldArray = $this->_query('SHOW COLUMNS FROM `'.$this->_tableName.'`;');
        foreach ($fieldArray as $values)
        {
            $fieldParts = explode('_', $values['Field']);
            $newFieldName = implode('', array_map('ucfirst', $fieldParts));

            $this->_fieldList[$newFieldName] = $values;
            $this->_valueList[$newFieldName] = '';
        }
    }

    /**
     * mixed __call ( string $Name )
     *
     * handles basic retrieval methods;
     *
     *      retrieveAll($options);
     *      retrieveByFieldName($value, $options);
     *
     * where FieldName is any field in the table
     * as UpperCamelCase and $options is an optional
     * array containing any of the following options;
     *
     *      'order_by' => 'field_name' OR
     *      'order_by' => array('field_1', 'field_2')
     *
     *      'group_by' => 'field_name' OR
     *      'group_by' => array('field_1', 'field_2')
     *
     *      'limit' => 10
     *      'offset' => 10
     *
     * handles basic getters and setters for fields;
     *
     *      ->getFieldName();
     *      ->setFieldName('value');
     *
     * @param  string      $modelName
     * @return SwiftTable
     * @access public
     * @static
     */
    public function __call($methodName, array $arguments)
    {
        /**
         * basic field retrieval methods for
         * peers
         */
        if (substr(strtolower($methodName), 0, 8) == 'retrieve' && $this->_instanceType == 'peer')
        {
            $pattern = "/(.)([A-Z])/"; 
            $replacement = "\\1 \\2";
            $fieldName = strtolower(str_replace(' ', '_', preg_replace($pattern, $replacement, substr($methodName, 8, strlen($methodName)))));

            /**
             * if retrieveAll there is no value
             * just options array
             */
            if ($fieldName == 'all')
            {
                return $this->_retrieve(array('all'), $arguments);
            }
            elseif (substr($fieldName, 0, 2) == 'by')
            {
                /**
                 * otherwise parse FieldName from
                 * the method, check it exists and
                 * run the retrieve
                 */
                $actualFieldName = str_replace('retrieveBy', '', $methodName);
                if (isset($this->_fieldList[$actualFieldName]))
                {
                    return $this->_retrieve(array($this->_fieldList[$actualFieldName]['Field']), $arguments);
                }
            }
        }
        elseif (substr($methodName, 0, 3) == 'get')
        {
            /**
             * field getters
             */
            $fieldName = substr($methodName, 3, strlen($methodName));
            if (isset($this->_fieldList[$fieldName]))
            {
                return $this->_valueList[$fieldName];
            }
        }
        elseif (substr($methodName, 0, 3) == 'set')
        {
            /**
             * field setters
             */
            $fieldName = substr($methodName, 3, strlen($methodName));
            if (isset($this->_fieldList[$fieldName]))
            {
                $this->_valueList[$fieldName] = $arguments[0];
            }
        }
    }

    /**
     * array _retrieve( array $fieldArray , array $arguments )
     *
     * method that handles any and all core 
     * retrieval queries. takes an array of fields
     * and an array of values and options to build
     * the query, then runs the method to hydrate
     * the objects into a results array
     *
     * @param  array  $fieldArray
     * @param  array  $arguments
     * @return array
     * @access protected
     */
    protected function _retrieve(array $fieldArray, array $arguments)
    {
        /**
         * where query building
         */
        if ($fieldArray[0] == 'all')
        {
            $options = isset($arguments[0]) ? $arguments[0] : array();
            $whereQuery = '';
        }
        else
        {
            $options = isset($arguments[1]) ? $arguments[1] : array();
            $valueArray = $arguments[0];

            if (count($fieldArray) == 1)
            {
                $whereQuery = ' WHERE '.$fieldArray[0].' = \''.mysql_real_escape_string($valueArray).'\'';
            }
            else
            {
                $whereQuery = ' WHERE ';
                foreach ($fieldArray as $index => $fieldName)
                {
                    $whereQuery .= $fieldName.' = \''.mysql_real_escape_string($valueArray[$index]).'\' && ';
                }
                $whereQuery = substr($whereQuery, 0, strlen($whereQuery)-4);
            }
        }

        if (!isset($options))
        {
            $options = array();
        }
        list($orderQuery, $groupQuery, $limitQuery) = $this->_buildFilterQuery($options);

        $fieldQuery = '*';
        if (isset($options['return_fields']) && count($options['return_fields']))
        {
            $fieldQuery = '';
            foreach ($options['return_fields'] as $field)
            {
                $fieldQuery .= $field.', ';
            }
            $fieldQuery = substr($fieldQuery, 0, strlen($fieldQuery)-2);
        }
        
        $query = 'SELECT '.$fieldQuery.' FROM `'.$this->_tableName.'`'.$whereQuery.$groupQuery.$orderQuery.$limitQuery;
        
        /**
         * return array of objects
         */
        return $this->_hydrateObjects($this->_query($query));
    }

    function _buildFilterQuery(array $options)
    {
        /**
         * ORDER BY
         */
        $orderQuery = '';
        if (isset($options['order_by']))
        {
            $orderQuery = ' ORDER BY ';
            if (is_array($options['order_by']))
            {
                $orderQuery .= implode(',', $options['order_by']);
            }
            else
            {
                $orderQuery .= $options['order_by'];
            }
        }

        /**
         * GROUP BY
         */
        $groupQuery = '';
        if (isset($options['group_by']))
        {
            $groupQuery = ' GROUP BY ';
            if (is_array($options['group_by']))
            {
                $groupQuery .= implode(',', $options['group_by']);
            }
            else
            {
                $groupQuery .= $options['group_by'];
            }
        }

        /**
         * LIMIT and OFFSET for paging
         */
        $limitQuery = '';
        if (isset($options['limit']))
        {
            $limitQuery .= ' LIMIT '.$options['limit'];
        }
        if (isset($options['offset']))
        {
            $limitQuery .= ' OFFSET '.$options['offset']; 
        }

        return array($orderQuery, $groupQuery, $limitQuery);
    }

    /**
     * void fromArray ( array $values )
     *
     * take an array of field => value and save
     * the values
     *
     * @param  array  $values
     * @return void
     * @access public
     */
    public function fromArray(array $values)
    {
        foreach ($values as $name => $value)
        {
            foreach ($this->_fieldList as $newName => &$fieldData)
            {
                if ($fieldData['Field'] == $name)
                {
                    $this->_valueList[$newName] = $value;
                    $fieldData['original_value'] = $value;
                }
            }
        }
        $this->_new = false;
    }

    /**
     * array _hydrateObjects ( array $resultsArray )
     *
     * take an array of results from _query() and
     * return an array of hydrated objects based on
     * the current model
     *
     * @param  array  $resultsArray
     * @return array
     * @access protected
     */
    protected function _hydrateObjects($resultsArray)
    {
        if (!$resultsArray)
        {
            return $resultsArray;
        }
        
        $objectArray = array();
        $className = $this->_modelName;
        if (count($resultsArray) == 1)
        {
            $newObj = new $className();
            $newObj->fromArray($resultsArray[0]);
            $newObj->_modelName = $this->_modelName;
            return $newObj;
        }
        else
        {
            foreach ($resultsArray as $row)
            {
                $newObj = new $className();
                $newObj->fromArray($row);
                $newObj->_modelName = $this->_modelName;
                $objectArray[] = $newObj;
            }
            return $objectArray;
        }
    }

    /**
     * array allFields()
     *
     * return array of all fields and their
     * values
     *
     * @return array
     * @access public
     */
    public function allFields()
    {
        return $this->_valueList;
    }

    public function fetchPairs($nameField, $valueField)
    {
        $allConfig = $this->_query('SELECT * FROM `'.$this->_tableName.'`;');
        $pairedArray = array();
        foreach ($allConfig as $config)
        {
            $pairedArray[$config[$nameField]] = $config[$valueField];
        }
        return $pairedArray;
    }

    public function select(array $fields, array $options = array())
    {
        /**
         * build where query
         */
        if (count($fields))
        {
            $whereQuery = ' WHERE ';
            foreach ($fields as $fieldName => $value)
            {
                if ($fieldName == 'search')
                {
                    if (count($value['and']))
                    {
                        $whereQuery .= '(';
                        foreach ($value['and'] as $searchField => $searchValue)
                        {
                            if (!empty($searchValue))
                            {
                                $whereQuery .= $searchField.' LIKE \'%'.mysql_real_escape_string($searchValue).'%\' && ';
                            }
                        }
                        $whereQuery = substr($whereQuery, 0, strlen($whereQuery)-4);
                        $whereQuery .= ') && ';
                    }
                    if (count($value['or']))
                    {
                        $whereQuery .= '(';
                        foreach ($value['or'] as $searchField => $searchValue)
                        {
                            if (!empty($searchValue))
                            {
                                $whereQuery .= $searchField.' LIKE \'%'.mysql_real_escape_string($searchValue).'%\' || ';
                            }
                        }
                        $whereQuery = substr($whereQuery, 0, strlen($whereQuery)-4).') && ';
                    }
                    break;
                }
                $whereQuery .= $fieldName.' = \''.mysql_real_escape_string($value).'\' && ';
            }
            $whereQuery = substr($whereQuery, 0, strlen($whereQuery)-4);
        }
        else
        {
            $whereQuery = '';
        }
        
        list($orderQuery, $groupQuery, $limitQuery) = $this->_buildFilterQuery($options);

        $fieldQuery = '*';
        if (isset($options['return_fields']) && count($options['return_fields']))
        {
            $fieldQuery = '';
            foreach ($options['return_fields'] as $field)
            {
                $fieldQuery .= $field.', ';
            }
            $fieldQuery = substr($fieldQuery, 0, strlen($fieldQuery)-2);
        }

        $query = 'SELECT '.$fieldQuery.' FROM `'.$this->_tableName.'`'.$whereQuery.$groupQuery.$orderQuery.$limitQuery;

        return $this->_hydrateObjects($this->_query($query)); 
    }

    public function count(array $fields = array(), array $options = array())
    {
        if (!empty($fields))
        {
            $whereQuery = ' WHERE ';
            foreach ($fields as $fieldName => $value)
            {
                if ($fieldName == 'search')
                {
                    if (count($value['and']))
                    {
                        $whereQuery .= '(';
                        foreach ($value['and'] as $searchField => $searchValue)
                        {
                            if (!empty($searchValue))
                            {
                                $whereQuery .= $searchField.' LIKE \'%'.mysql_real_escape_string($searchValue).'%\' && ';
                            }
                        }
                        $whereQuery = substr($whereQuery, 0, strlen($whereQuery)-4);
                        $whereQuery .= ') && ';
                    }
                    if (count($value['or']))
                    {
                        $whereQuery .= '(';
                        foreach ($value['or'] as $searchField => $searchValue)
                        {
                            if (!empty($searchValue))
                            {
                                $whereQuery .= $searchField.' LIKE \'%'.mysql_real_escape_string($searchValue).'%\' || ';
                            }
                        }
                        $whereQuery = substr($whereQuery, 0, strlen($whereQuery)-4).') && ';
                    }
                    break;
                }
                $whereQuery .= $fieldName.' = \''.mysql_real_escape_string($value).'\' && ';
            }
            $whereQuery = substr($whereQuery, 0, strlen($whereQuery)-4);
        }
        else
        {
            $whereQuery = '';
        }

        list($orderQuery, $groupQuery, $limitQuery) = $this->_buildFilterQuery($options);
       
        $query = 'SELECT COUNT(*) AS `num` FROM `'.$this->_tableName.'`'.$whereQuery.$groupQuery.$orderQuery.$limitQuery;

        $results = $this->_query($query);
        return $results[0]['num'];
    }

    public function save($debug = false)
    {
        if ($this->_new)
        {
            $this->_insert($debug);
        }
        else
        {
            $this->_update($debug);
        }
    }

    public function delete()
    {
        $query = 'DELETE FROM `'.$this->_tableName.'` WHERE `id` = '.addslashes($this->getId()).';';
        $this->_query($query);
    }

    protected function _update($debug = false)
    {
        $query = 'UPDATE `'.$this->_tableName.'` SET ';

        /**
         * build where query of previous values
         */
        $whereQuery = 'WHERE (';
        foreach ($this->_fieldList as $fieldName => $fieldData)
        {
            $whereQuery .= $fieldData['Field'].' = \''.mysql_real_escape_string($fieldData['original_value']).'\' && ';
        }
        $whereQuery = substr($whereQuery, 0, strlen($whereQuery)-4).')';

        /**
         * build value set query
         */
        foreach ($this->_fieldList as $fieldName => $fieldData)
        {
            $query .= $fieldData['Field'].' = \''.mysql_real_escape_string($this->_valueList[$fieldName]).'\', ';
        }
        $query = substr($query, 0, strlen($query)-2);

        $query = $query.' '.$whereQuery;

        if ($debug)
        {
            die($query);
        }
        
        $this->_query($query);

        if ($this->_new)
        {
            $this->_new = false;
        }
    }

    public function update(array $fields, array $selectorFields = array())
    {
        /**
         * build value query
         */
        $valueQuery = '';
        foreach ($fields as $fieldName => $fieldValue)
        {
            $valueQuery .= $fieldName.' = \''.mysql_real_escape_string($fieldValue).'\', ';
        }
        $valueQuery = substr($valueQuery, 0, strlen($valueQuery)-2);

        $whereQuery = '';
        if (count($selectorFields))
        {
            /**
             * build where query
             */
            $whereQuery = 'WHERE (';
            foreach ($selectorFields as $fieldName => $fieldValue)
            {
                $whereQuery .= $fieldName.' = \''.mysql_real_escape_string($fieldValue).'\' && ';
            }
            $whereQuery = substr($whereQuery, 0, strlen($whereQuery)-4).')';
        }
        
        $query = 'UPDATE `'.$this->_tableName.'` SET '.$valueQuery.$whereQuery;
        $this->_query($query);
    }

    protected function _insert($debug = false)
    {
        $fields = '';
        $values = '';
        foreach ($this->_fieldList as $fieldName => $fieldData)
        {
            $fields .= '`'.$fieldData['Field'].'`, ';
            $values .= '\''.mysql_real_escape_string($this->_valueList[$fieldName]).'\', ';
        }
        $fields = substr($fields, 0, strlen($fields)-2);
        $values = substr($values, 0, strlen($values)-2);
        
        $query = 'INSERT INTO `'.$this->_tableName.'` ('.$fields.') VALUES ('.$values.');';
        $this->_query($query);

        if (isset($this->_valueList['Id']))
        {
            $this->setId(mysql_insert_id($this->_conn));
        }
    }
}
